Homework 6 - Creating a resume database

This document contains information about a database containing people, skills, and peopleskills tables. These tables are represented as a Crows-Foot diagram and some lists along with descriptions for those sections. At the end there is a reflection and a read me.

GITHUB URL: https://github.com/cmsc-vcu/cmsc408-fa2025-hw6-CaptnKristmas

from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper, execute_ddl_from_file
from itables import show

# Load these variables from .env file.
config_map = {
  'user': "MYSQL_USERNAME",
  'password': "MYSQL_PASSWORD",
  'host': "MYSQL_HOSTNAME",
  'database': "HW6_DB_NAME"
}

cnx,config = create_db_wrapper( config_map )
  

def show_result_set( sql, **kwargs ):
    df = run_sql_and_return_df( cnx,sql )
    show(df,**kwargs)

Overview and description

The database contains 3 tables, people, skills, and peopleskills. The first covers the employees of the company, the second possible skills, and last tracks the skills earned. As such there is a relationship between people and peopleskills as well as skills and peopleskills. This allows us to connect the skills possibly earned with the employees and store what people have earned in people skills.

We were tasked with creating this database using mySQL, Python, a poetry shell, and multiple libraries listed in the readme. Following this we were tasked with completing this report that breaks down contents of the database via a Crows-Foot diagram, descriptions and some lists. Upon completion we rendered this document as an html that you may view.

Crows-foot diagram

This diagram represents the current database as a whole. Do to limitations we are unable to represent nullable and non nullable values and are also unable to represent varchar directly. This is instead just ignored and replaced with string respectively. We are able to represent the foreign keys and the primary keys on the diagram.

The connection between people and peopleskills as well as skills and peopleskills is represented via the diagram. It shows how exactly one person can have multiple skills tracked and exactly one skill can be tracked across many people.

erDiagram
    PEOPLE ||--o{ PEOPLESKILLS : "has"
    SKILLS ||--o{ PEOPLESKILLS : "tracks"

    PEOPLE {
        int id PK
        string first_name
        string last_name
        string email
        string linkedin_url
        string headshot_url
        string discord_handle
        string brief_bio
        date date_joined
    }

    SKILLS {
        int id PK
        string name
        string description
        string tag
        string url
        string time_commitment
    }

    PEOPLESKILLS {
        int id PK
        int skills_id FK
        int people_id FK
        date date_acquired
    }

Loading the database

ddl_file_name = "./my-ddl.sql"
messages,errors = execute_ddl_from_file( ddl_file_name, cnx)

if errors:
    for error in errors:
        print(f"{error}<br/>")
else:
    print(f"No errors detected while loading: {ddl_file_name}")

No errors detected while loading: ./my-ddl.sql

Examples of data in the database

The following sections provide an overview of the schema including table names, and number of rows and columns in each table.

For each table, a description of each table is presented along with it’s contents.

Tables and metrics in the database

TableName RecordCount ColumnCount
people 10 9
peopleskills 26 4
skills 8 6
Total Rows: 3, Total Columns: 3
x = show_result_set( sql )
Loading ITables v2.5.2 from the internet... (need help?)

People table

The people table is a list of employees at the company currently. It includes the id, first name, last name, email, linkedin url (if they have one), headshot url (if they have one), discord handle (if they have one), brief bio (if included), and the date joined at the company.

Below is data currently in the people table.

show_result_set( f"""
select * from people;
""" )
Loading ITables v2.5.2 from the internet... (need help?)

Skills table

The skills table is a list of skills that employees might have at this company. It includes the name, description, tag (for reference), url link for information, and estimated time commitment needed to gain the skill.

Below is data currently in the skills table.

show_result_set( f"""
select * from skills
""" )
Loading ITables v2.5.2 from the internet... (need help?)

PeopleSkills table

The peopleskills table is a link between emplopyees and skills. The table shows what skills have been acquired by which employees along with the date acquired. It allows the company to keep track of progress and assists in project assignment.

Below is data currently in the peopleskills table.

show_result_set(f"""
select * from peopleskills
""" )
Loading ITables v2.5.2 from the internet... (need help?)

Queries

List skill id, name and tag ordered by name

show_result_set( f"""
select id, name, tag from skills order by name
""" )
Loading ITables v2.5.2 from the internet... (need help?)

List people names and email addresses ordered by last_name

show_result_set( f"""
select first_name, last_name, email 
from people 
order by last_name
""" )
Loading ITables v2.5.2 from the internet... (need help?)

List skill names of Person 1

show_result_set( f"""
select  s.name as skill_name, ps. date_acquired
from peopleskills ps
join people p on ps.people_id = p.id
join skills s on ps.skills_id = s.id
where p.last_name = 'Person 1'
order by ps.date_acquired;
""" )
Loading ITables v2.5.2 from the internet... (need help?)

List people names with Skill 6

show_result_set( f"""
select p.first_name, p.last_name, 
    ps.date_acquired
from people p 
join peopleskills ps on p.id = ps.people_id 
where ps.skills_id = 6
order by date_acquired;
""" )
Loading ITables v2.5.2 from the internet... (need help?)

List names and email addresses of people without skills

show_result_set( f"""
select p.first_name, p.last_name, p.email 
from people p
left join peopleskills ps on p.id = 
    ps.people_id
where ps.id is null
order by last_name;
""" )
Loading ITables v2.5.2 from the internet... (need help?)

List names and tags of unused skills

show_result_set( f"""
select s.name, s.tag
from skills s
left join peopleskills ps on s.id = 
    ps.skills_id
where ps.id is null
order by s.tag;
""" )
Loading ITables v2.5.2 from the internet... (need help?)

Reflection

The assignment provides me with wonderful skills that are applicable to personal projects, organization or working at my future career. At the same time, there were major issues that crept up even after set up with dependencies randomly showing up as not installed that required uninstalls and restarts of Virtual Studio Code then reinstalls to fix. Took a while to discover this fix which added to the struggle quite a bit. This simply leads me to believe its more efficient to constantly update important code to new versions as opposed to dealing with poetry, pyenv and managing version control at all.

README

Homework 6 — Creating a Resume Database

Author: Vladimir Paraschiv
Course: CMSC 408 – Database Systems
Semester: Fall 2025
Date: October 24, 2025
Repository


📘 Overview

This project documents a Resume Database built from scratch using MySQL, Python, and Quarto. The system models individuals, their professional skills, and the skills each person has acquired over time.

The database consists of three key tables: - people – representing individuals or employees
- skills – cataloging skills with descriptions and tags
- peopleskills – a junction table linking people and skills with acquisition dates

The report demonstrates the design and relationships among these tables using a Crow’s Foot ER diagram, while showcasing SQL queries that retrieve and analyze the stored data. It integrates direct MySQL queries, Python data rendering, and interactive HTML output generated through Quarto.


🧩 Tools and Technologies

This project uses:

  • Quarto — for dynamic report generation combining SQL, Python, and Markdown
  • Mermaid — for the Crow’s Foot ER diagram representation
  • Python (3.11.6) — for SQL execution and data retrieval
  • itables — to display interactive HTML tables inside the report
  • SQLAlchemy / PyMySQL / dotenv — to handle MySQL connections and environment variables
  • Poetry — to manage dependencies and virtual environments
  • Pyenv — to isolate interpreter versions for consistent runtime behavior

🧠 Database Summary

The Resume Database contains three relational tables that together capture who works at the company, which skills exist, and who has acquired which skills:

  • people — stores employee demographic and contact information
  • skills — defines a catalog of technical and professional skills
  • peopleskills — records which people have which skills, and the date acquired

This schema forms a many-to-many relationship between people and skills, implemented via the peopleskills junction table.

Referential integrity is enforced through foreign keys: - peopleskills.people_id → people.id
- peopleskills.skills_id → skills.id


🧩 Repository Structure

cmsc408-fa2025-hw6-CaptnKristmas/
│
├── .vscode/ # VS Code workspace settings
│ └── settings.json # Python interpreter and linting settings
│
├── reports/
│ ├── report.qmd # Quarto report (main deliverable)
│ ├── _quarto.yml # Quarto project metadata
│ ├── my-ddl.sql # SQL DDL for table creation and inserts
│ ├── helpers.py # Helper utilities for connecting and querying MySQL
│ ├── _init__.py # (Marks reports as a package)
│ ├── .env # Environment variables for database connection
│ ├── .gitignore # Ignore files for Git
│ ├── report.html # Rendered HTML output
│ ├── report_files\libs # Quarto generated supporting files
│ └──  _pycache__/
│   ├── helpers.cpython-310.pyc # Compiled helper utilities
│   └── helpers.cpython-311.pyc # Compiled helper utilities
│
├── pyproject.toml # Poetry environment definition
├── poetry.lock # Poetry dependency lock file
├── .gitignore # Ignore files for Git
└── readME.md # This documentation file

⚙️ Setup Instructions for Fresh Development Environment

1. Environment Setup

Ensure you have the following installed:

  • Python 3.11+
  • Poetry
  • MySQL Server
  • Quarto
  • VS Code

Activate the Poetry environment:

poetry shell

Install dependencies:

poetry install

2. Configure Environment Variables

Create a .env file in the reports/ directory with your MySQL credentials:

MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_HOST=localhost
MYSQL_DB=your_database_name

3. Database Setup

Run the SQL DDL script to create tables and insert data.

4. Load Database

Execute the provided Python script to access and retrieve data from the database.

Expected output: No errors detected while loading: ./my-ddl.sql


🧪 Rendering Instructions

To render the Quarto report into a standalone HTML document, run in Virtual Studio Code terminal: cd ./reports and then: quarto render report.qmd

Alternatively, simply open the pre-rendered report.html file included in this repository.